using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

public class ExcelPercentageReader
{
    public List<Dictionary<string, double>> ReadNumericValuesFromNtoY(string filePath, string sheetName)
    {
        var results = new List<Dictionary<string, double>>();

        using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
        {
            IWorkbook workbook = Path.GetExtension(filePath).ToLower() == ".xls" 
                ? (IWorkbook)new HSSFWorkbook(fs) 
                : new XSSFWorkbook(fs);

            ISheet sheet = workbook.GetSheet(sheetName) ?? 
                throw new ArgumentException($"工作表 {sheetName} 不存在");

            IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();

            // 列范围设置（N=14列对应索引13，Y=25列对应索引24）
            const int startColIndex = 13;
            const int endColIndex = 24;

            for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                IRow row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                var rowData = new Dictionary<string, double>();
                
                for (int colIndex = startColIndex; colIndex <= endColIndex; colIndex++)
                {
                    ICell cell = row.GetCell(colIndex);
                    string columnName = GetExcelColumnName(colIndex + 1); // 转换为Excel列名

                    // 获取原始数值（自动处理百分比格式）
                    double numericValue = GetTrueNumericValue(cell, evaluator);
                    
                    rowData[columnName] = numericValue;
                }
                
                results.Add(rowData);
            }
        }

        return results;
    }

    private double GetTrueNumericValue(ICell cell, IFormulaEvaluator evaluator)
    {
        if (cell == null) return double.NaN;

        try
        {
            // 处理公式单元格
            if (cell.CellType == CellType.Formula)
            {
                cell = evaluator.EvaluateInCell(cell);
            }

            // 处理百分比格式的数值
            if (cell.CellStyle.GetDataFormatString().Contains("%"))
            {
                // 直接返回原始存储的十进制值（如显示50%实际存0.5）
                return cell.NumericCellValue; 
            }

            // 处理常规数值类型
            if (cell.CellType == CellType.Numeric)
            {
                return cell.NumericCellValue;
            }

            // 处理文本型百分比（如手工输入的"50%"）
            if (cell.CellType == CellType.String)
            {
                string strValue = cell.StringCellValue.Trim();
                if (strValue.EndsWith("%"))
                {
                    if (double.TryParse(strValue.TrimEnd('%'), 
                        NumberStyles.Any, 
                        CultureInfo.InvariantCulture, 
                        out double value))
                    {
                        return value / 100.0;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"单元格 {cell.Address} 读取错误: {ex.Message}");
        }

        return double.NaN;
    }

    private string GetExcelColumnName(int columnNumber)
    {
        int dividend = columnNumber;
        string columnName = string.Empty;

        while (dividend > 0)
        {
            int modulo = (dividend - 1) % 26;
            columnName = Convert.ToChar('A' + modulo) + columnName;
            dividend = (dividend - modulo) / 26;
        }

        return columnName;
    }
}

// 使用示例
var reader = new ExcelPercentageReader();
var data = reader.ReadNumericValuesFromNtoY("data.xlsx", "Sheet1");

// 打印结果
foreach (var row in data)
{
    Console.WriteLine("Row Values:");
    foreach (var kvp in row)
    {
        Console.WriteLine($"{kvp.Key}: {kvp.Value:F4}"); // 保留4位小数
    }
    Console.WriteLine(new string('-', 30));
}